package TableAdditions;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class DatabaseFunctions {
	static int customer_id;
	static int product_id;
	static float specialPrice;
	public static boolean previousSpecialPrice=false;
	
	
	
	
public static int getCustomerIdFromName(String customer_name){
		
		Statement stmt = null;
    	Connection conn = null;
    	ResultSet rss = null;
    	String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "qweasdzxc";
        
        try {
			conn = (Connection) DriverManager.getConnection(url, user, password);
			stmt = (Statement) conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
      
    	
        try {
			rss=stmt.executeQuery("SELECT customer_id from customer where customer_name='"+customer_name+"'");
			while(rss.next()){
				customer_id=rss.getInt("customer_id");
			}
			conn.close();
			//System.out.println("SelectedCustomer id= "+selectedCustomerId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

        
		
		return customer_id;
	}




public static float getSpecialPrice(int customer_id,int product_id){
	int counter=0;
	Statement stmt = null;
	Connection conn = null;
	ResultSet rss = null;
	String url = "jdbc:mysql://localhost:3306/mydb";
    String user = "root";
    String password = "qweasdzxc";
    
    try {
		conn = (Connection) DriverManager.getConnection(url, user, password);
		stmt = (Statement) conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
    
    
	
    try {
		rss=stmt.executeQuery("SELECT * from special_price where special_price_customer_id='"+customer_id+"' " +
				"and special_price_product_id='"+product_id+"'");
		while(rss.next()){
			specialPrice=rss.getFloat("special_price");
			previousSpecialPrice=true;
			counter++;
		}
		
		//System.out.println("SelectedCustomer id= "+selectedCustomerId);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
    
    if(counter==0){
    	try {
			rss=stmt.executeQuery("SELECT product_selling_price from product where product_id='"+product_id+"'");
			while(rss.next()){
				specialPrice=rss.getFloat("product_selling_price");
				previousSpecialPrice=false;
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }

    
	
	return specialPrice;
}


public static int getProductIdFromName(String product_name){
	
	Statement stmt = null;
	Connection conn = null;
	ResultSet rss = null;
	String url = "jdbc:mysql://localhost:3306/mydb";
    String user = "root";
    String password = "qweasdzxc";
    
    try {
		conn = (Connection) DriverManager.getConnection(url, user, password);
		stmt = (Statement) conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
  
	
    try {
		rss=stmt.executeQuery("SELECT product_id from product where product_name='"+product_name+"'");
		while(rss.next()){
			product_id=rss.getInt("product_id");
		}
		conn.close();
		//System.out.println("SelectedCustomer id= "+selectedCustomerId);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

    
	
	return product_id;
}





}
